package util;
import entity.Gushi;
import entity.User;
import org.apache.commons.dbcp2.BasicDataSource;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/*
JDBC封装:类包含连接池,数据库连接,和通用CURD操作
* */
public class DBUtil {
    static BasicDataSource dataSource = new BasicDataSource();

    static{
        //使用properties加载属性文件
        Properties prop = new Properties();
        try {
            InputStream is = DBUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            prop.load(is);

             dataSource.setDriverClassName(prop.getProperty("driverClassName"));
             dataSource.setUrl(prop.getProperty("url"));
             dataSource.setUsername(prop.getProperty("user"));
             dataSource.setPassword(prop.getProperty("password"));

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //获取数据库连接
    public static Connection getConnection(){
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
        } catch ( SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }



    //释放资源
    public static void close(Connection conn, Statement stat){
        if(conn != null){
            try {conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat != null){
            try {stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    //释放资源
    public static void close(Connection conn, Statement stat, ResultSet rs){
        if(conn != null){
            try {conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat != null){
            try {stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs != null){
            try {rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


/*========================================以下是JDBC CURD封装方法=============================================*/

    /*
    获取单个对象
    sql:传入sql语句
    clazz:类信息 如Student.class
    args:sql语句的参数
    * */
    public static <T> T getObject(String sql, Class<T> clazz,Object... args){
        T t=null;
        Connection conn=null;
        PreparedStatement pstm=null;
        ResultSet rs=null;
        try {
              conn=getConnection();
              pstm=conn.prepareStatement(sql);
             // select * from student where id=? and name=?
            // 123,tom

          if(args!=null){
              for (int i = 0; i < args.length; i++) {
                  pstm.setObject((i+1),args[i]);//遍历可变数组args,循环给pstm赋参数值
              }
          }

            rs=pstm.executeQuery();
            Field[] fields=clazz.getDeclaredFields();

           while(rs.next()){
               t=clazz.newInstance();//创建对应类的对象

               try {

                   for (int i = 0; i <fields.length ; i++) {
                       fields[i].setAccessible(true);//开启私有可访问
                       fields[i].set(t,rs.getObject(fields[i].getName()));
                       fields[i].setAccessible(false);
                   }

               } catch (IllegalAccessException e) {
                   e.printStackTrace();
               }
           }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(conn,pstm,rs);
        }

        return t;
    }



    /*
获取多个对象存入List集合
sql:传入sql语句
clazz:类信息 如Student.class
args:sql语句的参数
* */
    public static  <T> List<T> getList(String sql, Class<T> clazz, Object... args){
        List<T> t=null;
        T t2=null;
        Connection conn=null;
        PreparedStatement pstm=null;
        ResultSet rs=null;
        try {
            conn=getConnection();
            pstm=conn.prepareStatement(sql);
            // select * from student where id=? and name=?
            // 123,tom

            if(args!=null){
                for (int i = 0; i < args.length; i++) {
                    pstm.setObject((i+1),args[i]);//遍历可变数组args,循环给pstm赋参数值
                }
            }

            rs=pstm.executeQuery();
            Field[] fields=clazz.getDeclaredFields();
             int index=0;
            t =new ArrayList<>();
            while(rs.next()){
                t2=clazz.newInstance();
                ++index;
                try {
                    for (int i = 0; i <fields.length ; i++) {
                        fields[i].setAccessible(true);//开启私有可访问
                        fields[i].set(t2,rs.getObject(fields[i].getName()));
                        fields[i].setAccessible(false);

                    }
                    t.add(t2);

                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }


            return t;


        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(conn,pstm,rs);
        }

        return null;
    }

   /*
    插入单个对象
    sql:传入sql语句
    obj:是插入的对象信息
    * */

    public static  int insertObject(String sql,Object obj){
        Connection conn=null;
        PreparedStatement pstm=null;
        try {
            conn=getConnection();
            pstm=conn.prepareStatement(sql);
            Field[] field=obj.getClass().getDeclaredFields();
            int index=0;
            for (int i = 1; i < field.length; i++) {
                field[i].setAccessible(true);
                if(field[i].get(obj)!=null||"null".equals(field[i].get(obj))){
                    System.out.println(field[i].get(obj));
                    pstm.setObject(++index,field[i].get(obj));
                }
                field[i].setAccessible(false);
            }
              return pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }finally {
            close(conn,pstm);
        }

        return 0;
    }


       /*
    插入单个对象
    sql:传入sql语句
    obj:是插入的对象信息
    * */

    public static  int insertObjectList(String sql,List list){

        Connection conn=null;
        PreparedStatement pstm=null;
        try {
            conn=getConnection();
            pstm=conn.prepareStatement(sql);

            int line=0;
            for (int i = 0; i < list.size(); i++) {
                Field[] field=list.get(i).getClass().getDeclaredFields();
                int index=0;
                for (int j = 1; j < field.length; j++) {
                    field[j].setAccessible(true);
                    if(field[j].get(list.get(i))!=null||"null".equals(field[j].get(list.get(i)))){
                        System.out.println(field[j].get(list.get(i)));
                        pstm.setObject(++index,field[j].get(list.get(i)));
                    }
                    field[i].setAccessible(false);
                }
                line+=pstm.executeUpdate();
            }


            return line;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }finally {
            close(conn,pstm);
        }

        return 0;
    }




    /*
更新单个对象
sql:传入sql语句
obj:是要更新的对象信息
* */
    public static int updateObject(String sql,Object obj){
        Connection conn=null;
        PreparedStatement pstm=null;
        try {
            conn=getConnection();
            pstm=conn.prepareStatement(sql);
            Field[] field=obj.getClass().getDeclaredFields();
            int index=0;
            int d=0;
            for (int i = 1; i < field.length; i++) {
                field[0].setAccessible(true);
                System.out.println("===>>>"+field[0].get(obj));

                field[i].setAccessible(true);
                if(field[i].get(obj)!=null||"null".equals(field[i].get(obj))){
                        System.out.println(field[i].get(obj));
                        pstm.setObject(++index,field[i].get(obj));

                        if(index==field.length-1){
                                pstm.setObject(field.length,field[0].get(obj));
                        }
                }

                field[i].setAccessible(false);

            }
            return pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }finally {
            close(conn,pstm);
        }

        return 0;

    }

    /*
删除单个对象
sql:传入sql语句
obj:要删除的对象信息,一般传入主键id
* */
    public static int deleteObject(String sql,Object obj){
        Connection conn=null;
        PreparedStatement pstm=null;
        try {
            conn=getConnection();
            pstm=conn.prepareStatement(sql);
            pstm.setObject(1,obj);//只根据主键删除
            return pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(conn,pstm);
        }

        return 0;

    }


//    public static void main(String[] args) {
//        String sql="select * from user where id=?";
//        User user=getObject(sql, User.class,1);//通过id=1查询一个User对象
//        System.out.println(user);
//
//    }

}
